package schedule.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DataRequester {

	private Connection connection;

	public DataRequester() {
		try {

			Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

			// create connection to the database
			// use url found in JDBC connection properties
			// jdbc:derby://localhost:1527/sample;
			connection = DriverManager
					.getConnection("jdbc:derby://localhost:1527/sample");

			// create sql statement using the connection
			Statement statement = connection.createStatement();
		} catch (Exception ex) {
			System.out.println("connection failed");
		}

	}

	// create sql statement to pull only studentTimes who have the given advisor
	public ArrayList<InputTime> getStudentTimes(int advisorID) {
		System.out.println("----getStudentTimes----");
		try {
			Statement selectStudentSql = connection.createStatement();
			// Insert mark's sql stmt here
			String sql = "SELECT APP.STUDENTTIME.BANNERID, APP.STUDENTTIME.STDAY, APP.STUDENTTIME.STSTARTHOUR,APP.STUDENTTIME.STSTARTMIN,APP.STUDENTTIME.STENDHOUR,APP.STUDENTTIME.STENDMIN FROM APP.STUDENTTIME, APP.STUDENT, APP.ADVISOR WHERE APP.STUDENT.ADVISORID = APP.ADVISOR.ID AND APP.STUDENT.BANNERID = APP.STUDENTTIME.BANNERID AND APP.ADVISOR.ID = "
					+ advisorID;
			// String sql = "SELECT * FROM app.STUDENTTIME";
			ArrayList<InputTime> studentInputTimes = new ArrayList<InputTime>();
			ResultSet studentTimes = selectStudentSql.executeQuery(sql);
			while (studentTimes.next()) {
				int startHr = studentTimes.getInt("stStartHour");
				int startMn = studentTimes.getInt("stStartMin");
				int endHr = studentTimes.getInt("stEndHour");
				int endMn = studentTimes.getInt("stEndMin");
				int day = studentTimes.getInt("stDay");
				// int week = studentTimes.getInt("stWeek");
				String bannerID = studentTimes.getString("BannerId");
				InputTime freshInput = new InputTime(startHr, startMn, endHr,
						endMn, day);
				freshInput.setBannerID(bannerID);

				System.out.println("startHr " + freshInput.getStartHr() + " startMin " + freshInput.getStartMin() + " endHr " + freshInput.getEndHr() + " endMin " + freshInput.getEndMin() + " day "
						+ freshInput.getCurrentDay() + " bannerID "
						+ freshInput.bannerID);
				studentInputTimes.add(freshInput);
			}
			return studentInputTimes;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}

	}
	
	public HashMap<String, Student> getStudentEmails(HashMap<String, Student> oldStudents){
		HashMap<String, Student> newStudents = new HashMap<String, Student>();
		try{
			Statement selectEmailSql = connection.createStatement();
			String sql = "";
			for (Student s : oldStudents.values()){
				String sqlEmail = String.format("'%s'", s.bannerID);
				sql = "SELECT APP.STUDENT.SEMAILADDRESS FROM APP.STUDENT WHERE APP.STUDENT.BANNERID = " + sqlEmail;
				ResultSet studentEmail = selectEmailSql.executeQuery(sql);
				if (studentEmail.next()) s.email = studentEmail.getString("sEmailAddress");
				
				System.out.println(s.email);
				newStudents.put(s.bannerID, s);
			}				
		}catch(Exception ex){
			System.out.println(ex.getMessage());
		}		
		return newStudents;
	}

	public ArrayList<InputTime> getAdvisorTimes(int advisorID) {
		System.out.println("----getAdvisorTimes----");
		ArrayList<InputTime> advisorInputTimes = new ArrayList<InputTime>();
		try {
			Statement selectAdvisorSql = connection.createStatement();
			// only select advisor times based on advisorID arg
			ResultSet select = selectAdvisorSql
					.executeQuery("select * from app.ADVISORTIME where app.ADVISORTIME.ATADVISORID = " + advisorID);
			while (select.next()) {
				int startHour = select.getInt("atstarthour");
				int startMin = select.getInt("atstartmin");
				int endHour = select.getInt("atendhour");
				int endMin = select.getInt("atendmin");
				int endDay = select.getInt("atday");
				// int endWeek = select.getInt("atweek");
				int endAdvisorID = select.getInt("atAdvisorId");
				InputTime freshInput = new InputTime(startHour, startMin,
						endHour, endMin, endDay);
				freshInput.setCurrentDay(endDay);
				freshInput.setAdvisorID(endAdvisorID);
				System.out.println("startHr " + freshInput.getStartHr() + " startMin " + freshInput.getStartMin() + " endHr " + freshInput.getEndHr() + " endMin " + freshInput.getEndMin() + " day "
						+ freshInput.getCurrentDay() + " advisorID "
						+ freshInput.advisorID);
				advisorInputTimes.add(freshInput);
			}
			return advisorInputTimes;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}

	}

	public ArrayList<Advisor> getAdvisorNames() {
		ArrayList<Advisor> advisorNames = new ArrayList<Advisor>();
		try {
			Statement selectAdvisorSql = connection.createStatement();
			ResultSet select = selectAdvisorSql.executeQuery("select * from app.ADVISOR");
			while (select.next()) {
				int id = select.getInt("ID");
				String firstName = select.getString("aFirstName");
				String lastName = select.getString("aLastName");
				Advisor freshInput = new Advisor(id, firstName, lastName);				
				advisorNames.add(freshInput);				
			}			
			return advisorNames;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}
	}
	public void insertIntoAdvisor(List<InputTime> allTimes, int advisorID) {
		String sql = "";
		int i = 0;

		try {
			Statement insertTimes = connection.createStatement();
			while (i < allTimes.size()) {
				sql = "INSERT INTO app.AdvisorTime(atAdvisorId, atStartHour, atStartMin, atEndHour, atEndMin, atday) VALUES("
						+ advisorID
						+ ","
						+ convertStandardHrToMilHr(allTimes.get(i).startHr)
						+ ", "
						+ allTimes.get(i).startMn
						+ ", "
						+ convertStandardHrToMilHr(allTimes.get(i).endHr)
						+ ", " + allTimes.get(i).endMn + ", "
						// + calculateDayNum(allTimes.get(i).currentDay)
						+ allTimes.get(i).currentDay + ")";
				int r = insertTimes.executeUpdate(sql);
				i++;
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}

	}

	public void insertAdvisorName(List<Advisor> advisorList){
		String sql = "";
		int i = 0;
		
		try{
			Statement insertName = connection.createStatement();
			while (i < advisorList.size()) {
				//System.out.println(allTimes.get(i).currentDay.charAt(0));
				sql = "INSERT INTO app.Advisor(ID, aFirstName, aLastName) VALUES("
						+ advisorList.get(i).id
						+ ", "
						+ advisorList.get(i).firstName
						+ ", "
						+ advisorList.get(i).lastName
						+")";
				int r = insertName.executeUpdate(sql);
				i++;
			}
		}catch(Exception ex){
			System.out.println(ex.getMessage());
		}
		
	}
	public void insertIntoScheduledMeeting(InputTime newTime, String banner, int advisorID) {
		String sql = "";
		int i = 0;

		try {
			Statement insertTimes = connection.createStatement();
			//while (i < allTimes.size()) {
				// System.out.println(allTimes.get(i).currentDay.charAt(0));
				String sqlBanner = String.format("'%s'", banner);
				sql = "INSERT INTO app.ScheduledMeeting(smAdvisorId, smBannerId, smStartHour, smStartMin, smEndHour, smEndMin, smDay) VALUES("
						+ advisorID
						+ ", "
						+ sqlBanner
						+ ", "
						+ convertStandardHrToMilHr(newTime.startHr)
						+ ", "
						+ newTime.startMn
						+ ", "
						+ convertStandardHrToMilHr(newTime.endHr)
						+ ", " + newTime.endMn + ", "
						// + calculateDayNum(newTime.get(i).currentDay)
						+ newTime.currentDay

						+ ")";
				int r = insertTimes.executeUpdate(sql);
				i++;
			//}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}

	}
	
	public InputTime getScheduledTime(String bannerID){
		InputTime meetingTime = new InputTime();
		try {
			Statement selectAdvisorSql = connection.createStatement();
			String sqlBanner = String.format("'%s'", bannerID);
			String sql = "SELECT  app.ScheduledMeeting.smStartHour, app.ScheduledMeeting.smStartMin, app.ScheduledMeeting.smEndHour, app.ScheduledMeeting.smEndMin, app.ScheduledMeeting.smDay FROM app.ScheduledMeeting WHERE app.ScheduledMeeting.smBannerId = " + sqlBanner;
			System.out.println(sql);
			ResultSet scheduled = selectAdvisorSql.executeQuery(sql);
			while (scheduled.next()) {
				//smAdvisorId, smBannerId, smStartHour, smStartMin, smEndHour, smEndMin, smDay				
				int startHr = scheduled.getInt("smStartHour");
				int startMin = scheduled.getInt("smStartMin");
				int endHr = scheduled.getInt("smEndHour");
				int endMin = scheduled.getInt("smEndMin");
				int startDay = scheduled.getInt("smDay");								
				meetingTime = new InputTime(startHr, startMin, endHr, endMin, startDay);
				System.out.println("Requested Time startHr " + meetingTime.getStartHr() + " startMin " + meetingTime.getStartMin() + " endHr " + meetingTime.getEndHr() + " endMin " + meetingTime.getEndMin() + " day "
						+ meetingTime.getCurrentDay()); 
			}			
			return meetingTime;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}
	}

	public int convertStandardHrToMilHr(int standardHour) {
		if (standardHour < 8) {
			standardHour = standardHour + 12;
		} else {
			return standardHour;
		}
		return standardHour;
	}	
}
